﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Contacts.Data.Model;

namespace Contacts.Data.Data
{
    public class ContactsDataGroups
    {
       private string _appconfConnString = ConfigurationManager.AppSettings["DatabaseConnection"];

       public List<Groups> LoadAllGroups()
       {
          SqlConnection sqlConnectionToContactsDB = new SqlConnection(_appconfConnString);
          sqlConnectionToContactsDB.Open();
          SqlCommand sqlCommandSelectAll = new SqlCommand("spSelectAllGroups", sqlConnectionToContactsDB);
          sqlCommandSelectAll.CommandType = CommandType.StoredProcedure;
          DataTable dt = new DataTable();
          SqlDataAdapter da = new SqlDataAdapter(sqlCommandSelectAll);
          da.Fill(dt);

          List<Groups> groupses = new List<Groups>();
          foreach (DataRow row in dt.Rows)
          {
             Groups groupColumnNames = new Groups();
             groupColumnNames.GroupID = Convert.ToInt32(row["GroupID"]);
             groupColumnNames.GroupName = row["GroupName"].ToString();
             groupColumnNames.Icon = row["Icon"].ToString();
             groupColumnNames.Description = row["Description"].ToString();
             groupses.Add(groupColumnNames);
          }
          sqlConnectionToContactsDB.Close();
          return groupses;
       }

       public void SaveGroup(int groupID, string groupName, string icon, string description)
       {
          SqlConnection conn = new SqlConnection(_appconfConnString);
          conn.Open();
          SqlCommand comm;

          if (groupID == 0)
          {
             comm = new SqlCommand("spAddGroup", conn);
          }
          else
          {
             comm = new SqlCommand("spUpdateGroup", conn);
             comm.Parameters.AddWithValue("@GroupID", groupID);
          }

          comm.CommandType = CommandType.StoredProcedure;
          comm.Parameters.AddWithValue("@GroupName", groupName);
          comm.Parameters.AddWithValue("@Icon", icon);
          comm.Parameters.AddWithValue("@Description", description);
          comm.ExecuteNonQuery();
          conn.Close();
       }

       public void DeleteGroup(int ID)
       {
          Groups group = new Groups();
          group.GroupID = ID;
          SqlConnection conn = new SqlConnection(_appconfConnString);
          conn.Open();
          SqlCommand comm = new SqlCommand("spDeleteGroup", conn);
          comm.CommandType = CommandType.StoredProcedure;
          comm.Parameters.AddWithValue("@GroupID", group.GroupID);
          comm.ExecuteNonQuery();

          conn.Close();
       }


       public int CheckOfGroupName(string chkGroupName)
       {
          Groups groups = new Groups();
          groups.GroupName = chkGroupName;
          SqlConnection conn = new SqlConnection(_appconfConnString);
          conn.Open();
          SqlCommand comm = new SqlCommand("spCheckOfGroupName", conn);
          comm.CommandType = CommandType.StoredProcedure;
          comm.Parameters.AddWithValue("@chkGroupName", chkGroupName);
          int groupID = Convert.ToInt32(comm.ExecuteScalar());
          conn.Close();

          return groupID;
       }


    }
}